/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package qlvbdq.dconnection;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author tuan
 */
public class SQLServerConnect {

    String Host = "";    //thong tin host cua database
    String UserName = "";//Thong tin ten dang nhap cua database server
    String Password = "";//Thong tin password
    String Database = "";//Ten Database muon ket noi
    
    Connection connect  = null;   //Khoi tao connect bang null
    CallableStatement statement = null;  // Khoi tao statement de thuc thi 
    ResultSet result    = null;  // Khoi tao result set de chua du lieu
    
    private static SQLServerConnect _instance;
//    public static class SQLSeverConnectHolder {
//        public static final SQLSeverConnect 
//    }
    public static SQLServerConnect createInsance(String host, String username, String password, 
            String database) {
        if (_instance == null) {
            _instance = new SQLServerConnect(host, username, password, database);
        }
        return _instance;
    }
    public static SQLServerConnect getInstance() {
        if (_instance == null){
            _instance = new SQLServerConnect();
        }
        return _instance;
    }
    public SQLServerConnect() {
    
    }
    //Ham khoi tao truyen thong tin cua database
    public SQLServerConnect(String host, String username, String password, 
            String database) {
        this.Host = host;
        this.Password = password;
        this.UserName = username;
        this.Database = database; 
    }
    
    protected void driverTest() {
    
    }
    
    public Connection getConnection() {
        
        if (this.connect == null) {
            String url = "jdbc:sqlserver://" + this.Host + " :1433;databaseName=" + this.Database + ";integratedSecurity=true";
            try {
                this.connect = DriverManager.getConnection(url, this.UserName, this.Password);
            } catch (java.sql.SQLException e) {
                e.printStackTrace(System.out);
            }
        }
        return this.connect;
    }
    
    public CallableStatement getStatement(String SQLStoreName){
        try {
            this.statement = this.getConnection().prepareCall("{call " + SQLStoreName + "}");
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        return this.statement;
    }
    
    public ResultSet getDataFromStore (String SQLStoreName, Object[] objs) {
        try {
            //thuc thi cau lenh
            this.statement = this.getStatement(SQLStoreName);
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    this.statement.setObject(i + 1, objs[i]);
                }
            }
            
            this.result = this.statement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }
        return this.result;
    }
    
    //update 1 row
    public int updateDataToDatabase (String SQLStoreName, Object[] objs) {
        int rowAffected = 0;
        try {
            //this.getConnection().setAutoCommit(true);
            this.statement = this.getStatement(SQLStoreName);
            
            if (objs != null) {
                for (int i = 0; i < objs.length; i++) {
                    this.statement.setObject(i + 1, objs[i]);
                }
            }
            rowAffected = this.statement.executeUpdate();
            this.getConnection().commit();
        } catch (SQLException e) {
            rowAffected = 0;
//            try {
//                //this.getConnection().rollback();
//            } catch (SQLException ex) {
//                Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
//            }
            e.printStackTrace(System.out);
        }
        return rowAffected;
    }
    
    public int[] updateMoreDataToDatabase (String SQLStoreName, int _countParameter, Object[] objs) {
        int[] updateCounts = null;
        
        try {
            this.getConnection().setAutoCommit(true);
            this.statement = this.getStatement(SQLStoreName);
            if (objs != null) {
                for (int i = 0; i < objs.length / _countParameter; i++) {
                    for (int j = 0; j < _countParameter; j++) {
                        this.statement.setObject(j, objs[i * _countParameter + j]);
                    }
                    this.statement.addBatch();
                }
                updateCounts = this.statement.executeBatch();
                this.getConnection().commit();
            }
        } catch (SQLException e) {
            try {
                this.getConnection().rollback();
                e.printStackTrace(System.out);
            } catch (SQLException ex) {
                Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        
        return updateCounts;
    }
    
    public boolean updateMoreTableToDatabase(CallableStatement[] _arrCallStatement) {
        boolean res = false;
        try {
            this.getConnection().setAutoCommit(true);
            for (CallableStatement currentStatement : _arrCallStatement) {
                currentStatement.executeUpdate();
            }
            res = true;
            this.getConnection().commit();
        } catch (SQLException ex) {
            
            try {
                this.getConnection().rollback();
                Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
            } catch (SQLException ex1) {
                Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex1);
            }
        }
        
        return res;
    }
    
    public void Close() {
        try {
            //close result
            if (this.result != null && !this.result.isClosed()){
                this.result.close(); 
                this.result = null; 
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //close statement
            if (this.statement != null && !this.statement.isClosed()) {
                this.statement.close();
                this.statement = null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //close connect
            if (this.connect != null && !this.connect.isClosed()) {
                this.connect.close();
                this.connect = null;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SQLServerConnect.class.getName()).log(Level.SEVERE, null, ex);
        }
    } 
}
